<?php

namespace app\api\controller;

use app\admin\model\People as ModelPeople;
use app\common\controller\Frontend;
use app\common\model\Attachment;
use Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Cache;

class People extends Frontend
{
    protected array $noNeedLogin = ['*'];

    protected array $noNeedPermission = ['*'];

    public function initialize(): void
    {
        parent::initialize();
    }


    public function index(): void
    {
        $currentPage = $this->request->param('currentPage/d', 1);
        $pageSize = $this->request->param('pageSize/d', 1);
        $peopleData = ModelPeople::where('status', 1)->page($currentPage, $pageSize)->order('weigh desc')->find();
        // 处理数据，拼接存储地址
        $this->success('', $peopleData->toArray());
    }
    public function atta(): void
    {
        $currentPage = $this->request->param('currentPage/d', 1);
        $pageSize = $this->request->param('pageSize/d', 1);

        $peopleList = Attachment::page($currentPage, $pageSize)->select();

        $total = Attachment::count();

        $result = [
            'total' => $total,
            'pageSize' => $pageSize,
            'currentPage' => $currentPage,
            'last_page' => ceil($total / $pageSize),
            'data' => $peopleList
        ];

        $this->success('', $result);
    }
    public function genSql(): void
    {

        // use PhpOffice\PhpSpreadsheet\IOFactory;

        // 配置部分
        $excelFile = './九型人格题目.xlsx';    // Excel文件路径
        $outputFile = 'questions.sql';      // 输出SQL文件路径

        try {
            // 1. 加载Excel文件
            $spreadsheet = IOFactory::load($excelFile);
            $sheet = $spreadsheet->getActiveSheet();

            // 2. 准备SQL语句容器
            $sqlHeader = "CREATE TABLE IF NOT EXISTS nine_type_personality_questions (
                question_number INT PRIMARY KEY,
                option_a_text TEXT,
                option_a_type INT,
                option_b_text TEXT,
                option_b_type INT
            );\n\n";
            $sqlInserts = [];

            // 3. 遍历每一行数据（跳过标题行）
            foreach ($sheet->getRowIterator(2) as $row) { // 从第2行开始
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(true);

                // 提取单元格数据
                $data = [];
                foreach ($cellIterator as $cell) {
                    $data[] = $cell->getValue();
                }

                if (count($data) < 5) continue; // 跳过不完整行

                // 处理数据
                $questionNumber = (int)$data[0];
                $optionAText = addslashes($data[1]); // 转义单引号
                $optionAValue = $this->extractNumber($data[2]);
                $optionBText = addslashes($data[3]);
                $optionBValue = $this->extractNumber($data[4]);

                // 生成SQL
                $sqlInserts[] = sprintf(
                    "(%d, '%s', %d, '%s', %d)",
                    $questionNumber,
                    $optionAText,
                    $optionAValue,
                    $optionBText,
                    $optionBValue
                );
            }

            // 4. 组合完整SQL
            $fullSql = $sqlHeader;
            $fullSql .= "INSERT INTO nine_type_personality_questions 
                (question_number, option_a_text, option_a_type, option_b_text, option_b_type) VALUES\n";
            $fullSql .= implode(",\n", $sqlInserts) . ";";

            // 5. 写入文件
            file_put_contents($outputFile, $fullSql);

            echo "SQL文件已生成：<a href='$outputFile'>下载SQL文件</a>";
        } catch (Exception $e) {
            die("处理失败: " . $e->getMessage());
        }
    }
    /**
     * 从字符串中提取数字（例如："4号（E列）" → 4）
     */
    function extractNumber($str)
    {
        preg_match('/\d+/', $str, $matches);
        return isset($matches[0]) ? (int)$matches[0] : 0;
    }
}
